package cz.muni.fi.pb138.avradiation.parser;

import cz.muni.fi.pb138.avradiation.DB.DBUtils;
import cz.muni.fi.pb138.avradiation.Sensor.Sensor;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.Iterator;
import javax.sql.DataSource;

/**
 *Saving collection of sensors data into MySQL database
 *
 * @author Vladimir Cerven <v.cerven@gmail.com>
 */
public class StoreSensorsManagerDB implements StoreSensorManager {

    private DataSource ds = null;
    
    public void setDataSource(DataSource ds) {
        if (ds == null) {
            throw new IllegalArgumentException("Data source is null");
        }
        
        this.ds = ds;
    }
    
    private void checkDataSource() {
        if (ds == null) {
            throw new IllegalStateException("Datasource is null");
        }
    }
    
    @Override
    public void storeSensors(Collection<Sensor> sensors) {
        if (sensors == null) {
            throw new IllegalArgumentException("Sensors is null.");
        }

        Iterator<Sensor> sensorsIterator = sensors.iterator();
        Sensor sensor = null;
        while (sensorsIterator.hasNext()) {
            sensor = sensorsIterator.next();
            getSensorId(sensor);
            if (sensor.getId() == null || sensor.getId() == -1) {
                continue;
            }
            Iterator<Long> valuesIterator = sensor.getMeasuredValues().keySet().iterator();
            Long time = null;
            BigDecimal value = null;
            while (valuesIterator.hasNext()) {
                time = valuesIterator.next();
                value = sensor.getMeasuredValues().get(time);
                saveMeasuredValue(sensor.getId(), time, value);
            }
        }

    }

    /**
     * Save value into database
     *
     * @param id Id sensor, on which was value measured
     * @param time Time when was value measured
     * @param value Value
     */
    private void saveMeasuredValue(Long id, Long time, BigDecimal value) {
        checkDataSource();
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("insert into measured_value(mval, mtime, idsensor) values(?, ?, ?)");
            if(value == null){
                ps.setNull(1, Types.DECIMAL);
            }else{
                ps.setBigDecimal(1, value);
            }
            ps.setLong(2, time);
            ps.setLong(3, id);
            int count = ps.executeUpdate();
            if(count != 1){
                throw new SQLException("Error when inserting in DB");
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }
    }

    /**
     * Get sensor ID for given sensor
     * @param sensor Sensor
     */
    private void getSensorId(Sensor sensor) {
        checkDataSource();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        long locationId = getLocationId(sensor.getLocation());

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("select idsensor from sensor where idlocation=? and name like ?");
            ps.setLong(1, locationId);
            ps.setString(2, sensor.getName());
            rs = ps.executeQuery();
            if (rs.next()) {
                sensor.setId(rs.getLong("idsensor"));
            } else {
                long sensorId = createSensor(sensor, locationId);
                if (sensorId != -1) {
                    sensor.setId(sensorId);
                } else {
                    throw new IllegalArgumentException("Sensor ID is -1");
                }
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } catch (IllegalArgumentException ex) {
            System.err.println("Error when getting sensor ID: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }
    }

    /**
     * Creates sensor in database
     *
     * @param sensor Sensor
     * @param locationId Location ID
     * @return New sensor ID
     */
    private long createSensor(Sensor sensor, long locationId) {
        checkDataSource();
        if (sensor == null || locationId == -1) {
            return -1;
        }
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("insert into sensor(name, unit, idlocation) values(?, ?, ?)");
            ps.setString(1, sensor.getName());
            ps.setString(2, sensor.getUnit());
            ps.setLong(3, locationId);
            ps.executeUpdate();
            long sensorId = DBUtils.getId(ps.getGeneratedKeys());
            if (sensorId != -1) {
                sensor.setId(sensorId);
                return sensorId;
            } else {
                throw new IllegalArgumentException("Sensor ID is -1.");
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } catch (IllegalArgumentException ex) {
            System.err.println("Error when getting ID: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }

        return -1;
    }

    /**
     * Get location ID for given location
     *
     * @param location Location
     * @return Location ID
     */
    private long getLocationId(String location) {
        checkDataSource();
        if (location == null) {
            throw new IllegalArgumentException("Location is null");
        }
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("select idlocation from location where name like ?");
            ps.setString(1, location);
            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getLong("idlocation");
            } else {
                return createLocation(location);
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }

        return -1;
    }

    /**
     * Creates location in database
     *
     * @param location Location
     * @return New location ID
     */
    private long createLocation(String location) {
        checkDataSource();
        if (location == null) {
            throw new IllegalArgumentException("Location is null");
        }
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = ds.getConnection();
            ps = conn.prepareStatement("insert into location(name) values(?)");
            ps.setString(1, location);
            ps.executeUpdate();
            long locationId = DBUtils.getId(ps.getGeneratedKeys());
            if (locationId != -1) {
                return locationId;
            } else {
                throw new IllegalArgumentException("Location ID is -1");
            }
        } catch (SQLException ex) {
            System.err.println("Preparation or execution of statement failed: "
                    + ex.getMessage());
        } catch (IllegalArgumentException ex) {
            System.err.println("Error when getting ID: "
                    + ex.getMessage());
        } finally {
            DBUtils.closeConnection(conn);
        }

        return -1;
    }
}
